'''
Created on 2011-8-31

@author: Administrator
'''

import logging
import time
import threading
import db.dbprotocol


from sqlalchemy import *

LOAD_TEMPLATE_SQL = \
'''SELECT TestPlan.*, DynamicTestType.SCXML
FROM TestPlan 
LEFT JOIN  DynamicTestType 
on TestPlan.DynamicTestTypeid = DynamicTestType.id 
where TestPlan.Disabled = 0'''

# TaskFlow
LOAD_FLOW_SQL = \
'''SELECT TaskFlowPlan.*, TaskFlowNode.* 
FROM TaskFlowPlan LEFT JOIN TaskFlowNode 
on TaskFlowPlan.id = TaskFlowNode.TaskFlowId'''

LOAD_FLOW_TEMPLATE_SQL = \
'''
SELECT TaskFlowPlan.* FROM TaskFlowPlan
'''

SELECT_NODE_INFO_SQL = \
'''
SELECT TaskFlowNode.* FROM TaskFlowNode WHERE TaskFlowId = {0}
'''


class Opertor():
    engine = None
    client_t = None
    exits = False
    handlerequest = {}
    
    def __init__(self, request, respond):
        self.request = request
        self.respond = respond
        self.handlerequest[db.dbprotocol.update_machine_request] = self.UpdateMachineInfo
        self.handlerequest[db.dbprotocol.get_cases_request] = self.GetCaseInfo
        self.handlerequest[db.dbprotocol.insert_task_request] = self.InsertTask
        self.handlerequest[db.dbprotocol.update_task_request] = self.UpdateTask
        self.handlerequest[db.dbprotocol.insert_os_request] = self.InsertOS
        self.handlerequest[db.dbprotocol.get_tasktemplate_request] = self.GetTaskTemplate
        self.handlerequest[db.dbprotocol.update_subtasks_request] = self.UpdateSubtaskInfo
        # task flow
        self.handlerequest[db.dbprotocol.get_taskflowtemplate_request] = self.GetTaskFlowTemplate
        self.handlerequest[db.dbprotocol.insert_taskflow_request] = self.InsertTaskFlow
        self.handlerequest[db.dbprotocol.update_taskflow_request] = self.UpdateTaskFlow
#        self.handlerequest[db.dbprotocol.insert_taskflow_respond] = self.InsertTaskFlowRespond
        
        self.thread = None
        
    def Init(self, url):
        self.engine = create_engine(url, echo=False)
        logging.getLogger('sqlalchemy.engine').setLevel(logging.WARN)
        
        self.client_t = Table('Machine', MetaData(self.engine), autoload=True)
        self.tasktemplate_t = Table('TestPlan', MetaData(self.engine), autoload=True)
        self.task_t = Table('TestTask', MetaData(self.engine), autoload=True)
        self.subtask_t = Table('SubTask', MetaData(self.engine), autoload=True)
        self.os_t = Table('OS', MetaData(self.engine), autoload = True)
        # task flow
        self.taskflow_t = Table('TaskFlowTasks', MetaData(self.engine), autoload=True)
        self.tasknoderesult_t = Table('TaskFlowNodeResult', MetaData(self.engine), autoload=True)
#        print(self.task_t.columns)
    
    def Uninit(self):
        self.exits = True
        self.thread.join()
        logging.info('db thread exits')
        self.engine.dispose()
        
        
    def LoadAllCaseTask(self):
        tasklist = []
        result = None
        try:
            result = self.engine.execute(LOAD_TEMPLATE_SQL)
            tasklist = result.fetchall()
            #result  = self.tasktemplate_t.select(self.tasktemplate_t.c.Disabled == False).execute()
            #tasklist = result.fetchall()
            
        except Exception as e:
            logging.exception(e)
        finally:
            if result:
                result.close()
                
        return tasklist
    
    # TaskFlow 
    def LoadAllTaskFlow(self):
        
        task_flow_templates = {}
        result_template = None
        result_node = None
        try:
            task_flow_list = []
            node_list = []
            result_template = self.engine.execute(LOAD_FLOW_TEMPLATE_SQL)
            task_flow_list = result_template.fetchall()
            for v in task_flow_list:
                node_sql = SELECT_NODE_INFO_SQL.format(v.Id)
                result_node = self.engine.execute(node_sql)
                node_list = result_node.fetchall()
                task_flow_templates[v.Id] = {'template_info':v, 'node_info':[]}
                for node in node_list:
                    task_flow_templates[v.Id]['node_info'].append(node)
        except Exception as msg:  
            logging.exception(msg)     
        finally:
            if result_template:
                result_template.close()
            if result_node:
                result_node.close()
        return task_flow_templates
        
        
    def LoadOSList(self):
        OSlist = []
        result = None
        try:
            result = self.engine.execute('select * from OS')
            OSlist = result.fetchall()
        except Exception as e:
            logging.exception(e)
        finally:
            if result:
                result.close()
                
        return OSlist
        
    def Start(self):
        self.thread = threading.Thread(target=self._MainLoop)
        self.thread.start()
        
    def UpdateMachineInfo(self, request):
        info = request.data
        result = None
        insertresult = None
        respond = db.dbprotocol.MachineDbIDRespond()
        respond.connectid = request.connectid
        try:
            result = self.client_t.select(self.client_t.c.MacAddress == info['MacAddress']).execute()
            rows = result.fetchall()
            count = len(rows)
            if len(rows) > 1:
                raise ValueError('same multiple macaddress in db')
            
            if count == 0:
                clientinsert = self.client_t.insert()
                insertresult = clientinsert.execute(**info)
                respond.dbid = insertresult.inserted_primary_key[0]
                respond.enable = False
                respond.special = False
            else:
                respond.enable = (not rows[0].Disabled)
                respond.dbid = rows[0].Id
                respond.groupid = rows[0].MachineGroupId
                respond.special = rows[0].Special
                
                clientupdate = self.client_t.update(self.client_t.c.MacAddress == info['MacAddress'])
                clientupdate.execute(**info).close()
        except Exception:
            logging.exception('update machine info error')
        finally:
            if result:
                result.close()
            if insertresult:
                insertresult.close()
            if request.isrespond:
                self.respond.put(respond)

    def _MainLoop(self):
        while(not self.exits or not self.request.empty()):
            try:
                if self.request.empty():
                    time.sleep(0.2)
                    continue
                request = self.request.get()
                self.handlerequest[request.id](request)
                
            except Exception as e:
                logging.exception(e)
    
    
    def GetCaseInfo(self, request):
        respond = db.dbprotocol.GetCaseRespond()
        result = None
        try:
            respond.taskid = request.taskid
            result = self.engine.execute(request.sql)
            for v in result:
                respond.cases.append(v.RunParameter)
        except Exception as e:
            logging.exception(e)
            respond.result = False
        finally:
            if result:
                result.close()
            self.respond.put(respond)
    
    def InsertTaskFlow(self, request):
        '''
        Insert task flow to db.
        '''
        result = None
        respond = db.dbprotocol.InsertTaskFlowRespond()
        respond.result = False
        try:
            respond.taskid = request.taskid
            i = self.taskflow_t.insert()
            result = i.execute(FlowPlanId=request.templateid,
                               FlowTaskStatusId=request.status)
            respond.dbid = result.inserted_primary_key[0]
            respond.result = True
        except Exception:
            logging.exception('insert task flow info to db fail!')
        finally:
            if result:
                result.close()
            self.respond.put(respond)
            
    
    def InsertTaskFlowRespond(self, respond):
        pass
  
            
    def UpdateTaskFlow(self, request):
        '''
        update task flow
        '''
        try:
            task_flow_insert = self.taskflow_t.update(self.taskflow_t.c.Id == request.taskid)
            task_flow_insert.execute(**request.task_flow_data).close()
            for v in request.node_datas:
                i = self.tasknoderesult_t.insert()
                i.execute(**v).close()
        except Exception:
            logging.exception('Update task flow fail!')
            
            
    
    def InsertTask(self, request):
        result = None
        respond = db.dbprotocol.InsertTaskRespond()
        respond.result = False
        try:
            respond.taskid = request.taskid
            i = self.task_t.insert()
            result = i.execute(PlanId=request.templateid,
                               TaskStatusId=request.status)
            respond.dbid = result.inserted_primary_key[0] 
            respond.result = True
        except Exception:
            logging.exception('insert fail')
        finally:
            if result:
                result.close()
            self.respond.put(respond)
        
    def UpdateTask(self, request):
        try:
            taskinsert = self.task_t.update(self.task_t.c.Id == request.taskid)
            taskinsert.execute(**request.data).close()
        except Exception:
            logging.exception('update task fail')
    

    def InsertOS(self, request):
        result = None
        respond = db.dbprotocol.InsertNewOSRespond()
        respond.result = False
        try:
            os_insert = self.os_t.insert()
            result = os_insert.execute(Name = request.name)
            respond.dbid = result.inserted_primary_key[0]
            respond.result = True
            respond.name = request.name
        except Exception:
            logging.exception('INSERT INTO OS (Name) values (%s)' % request.name)
        finally:
            if result:
                result.close()
            self.respond.put(respond)
    
    def GetTaskFlowTemplate(self, request):
        '''
        get task flow template.
        '''
        respond = db.dbprotocol.GetTaskFlowTemplateInfoRespond()
        respond.result = False
        respond.templateid = request.templateid
        try:
            task_flow = {}
            result_template = self.engine.execute('SELECT * FROM TaskFlowPlan where id = %d' % (request.templateid))
            task_flow_list = result_template.fetchall()
            if len(task_flow_list) == 1:
                respond.result  = True
                v =  task_flow_list[0]
                node_sql = SELECT_NODE_INFO_SQL.format(v.Id)
                result_node = self.engine.execute(node_sql)
                node_list = result_node.fetchall()
                task_flow = {'template_info':v, 'node_info':[]}
                for node in node_list:
                    task_flow['node_info'].append(node)
                respond.info = {request.templateid:task_flow}
        except Exception:
            logging.exception('get task flow template error!')
        finally:
            self.respond.put(respond)
            

            
                
    def GetTaskTemplate(self, request):
        respond = db.dbprotocol.GetTaskTemplateInfoRespond()
        respond.result = False
        respond.templateid = request.templateid
        try:
            sql = LOAD_TEMPLATE_SQL + ('  and TestPlan.id = %s' % request.templateid)
            result = self.engine.execute(sql)
            infos = result.fetchall()
            if len(infos) == 1:
                respond.result = True
                respond.info = infos[0]
        except Exception:

            logging.exception('get task template error')
        finally:
            if result:
                result.close()
            self.respond.put(respond)
            
    def UpdateSubtaskInfo(self, request):
        result = None
        try:
            for v in request.infos:
                result = self.subtask_t.select(and_(self.subtask_t.c.TaskId == v['TaskId'],
                                      self.subtask_t.c.TaskStatusId == v['TaskStatusId'])).execute()
                rows = result.fetchall()
                count = len(rows)
                if len(rows) > 1:
                    raise ValueError('same multiple subtask in db')
            
                if count == 0:
                    self.subtask_t.insert().execute(**v).close()
                else:
                    self.subtask_t.update(and_(self.subtask_t.c.TaskId == v['TaskId'],
                                      self.subtask_t.c.TaskStatusId == v['TaskStatusId'])).execute(**v).close()
        except Exception:
            logging.exception('update subtask error')
        finally:
            if result != None:
                result.close()
    
if __name__ == '__main__':
    a = Opertor(None, None)
    a.Init(r'mssql://sa:123456@127.0.0.1/autotest')
    
#    r = db.dbprotocol.UpdateSubTasksInfo()
#    r.infos = [{'TaskId':5005, 'TaskStatusId':1, 'Ip':'1', 'MacAddress':'22'}]
#    print(a.UpdateSubtaskInfo(r))
#    
#    request  = db.dbprotocol.InsertTaskRequest()
#    request.templateid = 3
#    request.status = 1
#    a.InsertTask(request)
    
#    rf = db.dbprotocol.InsertTaskFlowRequest()
#    rf.taskid = 1
#    rf.createtime = ''
#    rf.status = 1
#    rf.templateid = 3
#    a.InsertTaskFlow(rf)
    flow_template = a.LoadAllTaskFlow()
    print flow_template
    print 'finish'
    
    
